# 从阿里云的mysql中下载最新规则，创建在本地数据库当中
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# 连接本地数据库
import json
import pandas as pd
from jbar import bar
from sqlalchemy import create_engine, text
import re
import sys
import os
sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))
# 读取 .env 文件
from config import create_db_engine
# 创建数据库引擎
engine = create_db_engine()

# 连接阿里云数据库
# ALIYUN_DB_URL = "mysql+pymysql://justle:JustleJustle5758@rm-wz9nar188ji5w52732o.mysql.rds.aliyuncs.com:3306/wanda"
# 从 env.xlsx 文件中获取 ALIYUN_USER/ALIYUN_PASSWORD/ALIYUN_HOST/ALIYUN_PORT/ALIYUN_DB_NAME
env_aliyun = pd.read_excel('env.xlsx', sheet_name='db').set_index('KEY')['VALUE'].to_dict()
ALIYUN_USER = env_aliyun['ALIYUN_USER']
ALIYUN_PASSWORD = env_aliyun['ALIYUN_PASSWORD']
ALIYUN_HOST = env_aliyun['ALIYUN_HOST']
ALIYUN_PORT = env_aliyun['ALIYUN_PORT']
ALIYUN_DB_NAME = env_aliyun['ALIYUN_DB_NAME']
ALIYUN_DB_URL = f"mysql+pymysql://{ALIYUN_USER}:{ALIYUN_PASSWORD}@{ALIYUN_HOST}:{ALIYUN_PORT}/{ALIYUN_DB_NAME}"

# 从阿里云数据库中全量下载 rule_flatten, rule_package 两个表
print("Downloading stable tables from Aliyun MySQL...")
ruleFlatten = pd.read_sql("SELECT * FROM rule_flatten", ALIYUN_DB_URL)
rulePackage = pd.read_sql("SELECT * FROM rule_package", ALIYUN_DB_URL)
stableIcd10 = pd.read_sql("SELECT * FROM icd10", ALIYUN_DB_URL)
# 去掉 ID 列
stableIcd10.drop(columns=["id"], inplace=True)
stableIcd9 = pd.read_sql("SELECT * FROM icd9", ALIYUN_DB_URL)
# 去掉 ID 列
stableIcd9.drop(columns=["id"], inplace=True)
stableServ = pd.read_sql("SELECT * FROM stable_serv", ALIYUN_DB_URL)
stableMedi = pd.read_sql("SELECT * FROM stable_medi", ALIYUN_DB_URL)

# 判断 rule_flatten, rule_package 两个表在本地数据库中是否存在，如果存在则删除（ORACLE语句）
print("Dropping existing tables in local database if they exist...")
with engine.connect() as conn:
    conn.execute(text("""
        BEGIN
            EXECUTE IMMEDIATE 'DROP TABLE rule_flatten';
        EXCEPTION
            WHEN OTHERS THEN
                IF SQLCODE != -942 THEN
                    RAISE;
                END IF;
        END;
    """))
    conn.execute(text("""
        BEGIN
            EXECUTE IMMEDIATE 'DROP TABLE rule_package';
        EXCEPTION
            WHEN OTHERS THEN
                IF SQLCODE != -942 THEN
                    RAISE;
                END IF;
        END;
    """))
    conn.execute(text("""
        BEGIN
            EXECUTE IMMEDIATE 'DROP TABLE STABLE_ICD10';
        EXCEPTION
            WHEN OTHERS THEN
                IF SQLCODE != -942 THEN
                    RAISE;
                END IF;
        END;
    """))
    conn.execute(text("""
        BEGIN
            EXECUTE IMMEDIATE 'DROP TABLE STABLE_ICD9';
        EXCEPTION
            WHEN OTHERS THEN
                IF SQLCODE != -942 THEN
                    RAISE;
                END IF;
        END;
    """))
    conn.execute(text("""
        BEGIN
            EXECUTE IMMEDIATE 'DROP TABLE STABLE_SERV';
        EXCEPTION
            WHEN OTHERS THEN
                IF SQLCODE != -942 THEN
                    RAISE;
                END IF;
        END;
    """))
    conn.commit()
    conn.execute(text("""
        BEGIN
            EXECUTE IMMEDIATE 'DROP TABLE STABLE_MEDI';
        EXCEPTION
            WHEN OTHERS THEN
                IF SQLCODE != -942 THEN
                    RAISE;
                END IF;
        END;
    """))
    conn.commit()

# 创建 rule_flatten表，用ORACLE语句
with engine.connect() as conn:
    print("Creating rule_flatten table in local database...")
    conn.execute(text("""
        CREATE TABLE rule_flatten (
            id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
            rule_id VARCHAR2(50 CHAR) NOT NULL,
            key VARCHAR2(1000 CHAR),
            type VARCHAR2(50 CHAR),
            value VARCHAR2(4000 CHAR)
        )
    """))
    conn.commit()
# 创建 rule_package表，用ORACLE语句
with engine.connect() as conn:
    print("Creating rule_package table in local database...")
    conn.execute(text("""
        CREATE TABLE rule_package (
            package VARCHAR2(255 CHAR),
            value_raw VARCHAR2(255 CHAR)
        )
    """))
    conn.commit()
# 创建 icd10 表，按照 stableIcd10 的结构
with engine.connect() as conn:
    print("Creating icd10 table in local database...")
    conn.execute(text("""
        create table STABLE_ICD10
        (
            dx      VARCHAR2(100) not null,
            dxname  VARCHAR2(1000),
            lv0     VARCHAR2(100),
            lv1     VARCHAR2(100),
            lv2     VARCHAR2(100),
            lv3     VARCHAR2(100),
            lv4     VARCHAR2(100),
            lv0name VARCHAR2(1000),
            lv1name VARCHAR2(1000),
            lv2name VARCHAR2(1000),
            lv3name VARCHAR2(1000),
            lv4name VARCHAR2(1000)
        )
    """))
    conn.commit()
# 创建 icd9 表，按照 stableIcd9 的结构
with engine.connect() as conn:
    print("Creating icd9 table in local database...")
    conn.execute(text("""
        create table STABLE_ICD9
        (
            tx      VARCHAR2(100) not null,
            txname  VARCHAR2(1000),
            lv0     VARCHAR2(100),
            lv1     VARCHAR2(100),
            lv2     VARCHAR2(100),
            lv3     VARCHAR2(100),
            lv0name VARCHAR2(1000),
            lv1name VARCHAR2(1000),
            lv2name VARCHAR2(1000),
            lv3name VARCHAR2(1000)
        )
    """))
    conn.commit()
# 创建 stable_serv 表，按照 stableServ 的结构
with engine.connect() as conn:
    print("Creating stable_serv table in local database...")
    conn.execute(text("""
        create table STABLE_SERV
        (
            item_j_code VARCHAR2(100) not null,
            item_j_name VARCHAR2(1000),
            item_type   VARCHAR2(100),
            bz          VARCHAR2(4000)
        )
    """))
    conn.commit()
# 创建 stable_medi 表，按照 stableMedi 的结构
with engine.connect() as conn:
    print("Creating stable_medi table in local database...")
    conn.execute(text("""
        create table STABLE_MEDI
        (
            item_code_ptn VARCHAR2(50) not null,
            item_name VARCHAR2(500),
            bz   VARCHAR2(4000)
        )
    """))
    conn.commit()

# 将 rule_flatten 数据写入本地数据库
print("Inserting data into rule_flatten table...")
ruleFlatten.to_sql('rule_flatten', engine, if_exists='append', index=False)

# 将 rule_package 数据写入本地数据库
print("Inserting data into rule_package table...")
rulePackage.to_sql('rule_package', engine, if_exists='append', index=False)

# 将 stableIcd10 数据写入本地数据库
print("Inserting data into icd10 table...")
stableIcd10.to_sql('stable_icd10', engine, if_exists='append', index=False)

# 将 stableIcd9 数据写入本地数据库
print("Inserting data into icd9 table...")
stableIcd9.to_sql('stable_icd9', engine, if_exists='append', index=False)

# 将 stableServ 数据写入本地数据库
print("Inserting data into stable_serv table...")
stableServ.to_sql('stable_serv', engine, if_exists='append', index=False)

# 将 stableMedi 数据写入本地数据库
print("Inserting data into stable_medi table...")
stableMedi.to_sql('stable_medi', engine, if_exists='append', index=False)

print("Data insertion complete.")